Finding "bad" queries using SQL Trace ??

[Manoj]

I am optimising a application and need to find the queries which are taking lot of time to execute and which are scanning through many records (probably because indexing not proper).

I think that the tool to do this is SQL Trace. I created a trace but don't know how to interpret the data or what to look for.

Can someone please share their experience as to what info/data can one look at to find these "bad" queries.

Thanks!
- Manoj

[tongbee]

Turn on the query option before You run your query or store procedure in the Isqlw, select the show plan option. This will trace all your sql stmts.


------------
Manoj at 5/1/00 10:13:54 AM

I am optimising a application and need to find the queries which are taking lot of time to execute and which are scanning through many records (probably because indexing not proper).

I think that the tool to do this is SQL Trace. I created a trace but don't know how to interpret the data or what to look for.

Can someone please share their experience as to what info/data can one look at to find these "bad" queries.

Thanks!
- Manoj

[Manoj]

The method suggested by you will work for optimising individual SQLs or stored procedures. I am not looking for that.

Its possible to trace (ie. capture info about all running statments) a particular server and then from the trace identify the queries which are taking long time to execute and are scanning many rows.

Can anyone please share thier experience on this.....

Thanks!
Manoj


------------
tongbee at 5/1/00 10:21:00 AM

Turn on the query option before You run your query or store procedure in the Isqlw, select the show plan option. This will trace all your sql stmts.


------------
Manoj at 5/1/00 10:13:54 AM

I am optimising a application and need to find the queries which are taking lot of time to execute and which are scanning through many records (probably because indexing not proper).

I think that the tool to do this is SQL Trace. I created a trace but don't know how to interpret the data or what to look for.

Can someone please share their experience as to what info/data can one look at to find these "bad" queries.

Thanks!
- Manoj